from tortoise.expressions import Q
from app.settings import settings
import random
import string
import pandas as pd
from io import BytesIO
import os
from app.models.admin import Downloads
from app.core.ctx import CTX_USER_ID
from urllib.parse import urlparse
import fitz  # PyMuPDF
import uuid
import zipfile
from app.log.log import logger
from openpyxl.styles import Alignment

def random_filename():
    random_str = ''.join(random.choices(string.ascii_letters + string.digits, k=10))
    return random_str

async def create_excel(name:str,data,total_amount):
    # 创建一个 DataFrame
    df = pd.DataFrame(data)
    # 将 DataFrame 写入 BytesIO 对象
    output = BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        # df.to_excel(writer, index=False, sheet_name='Sheet1')
        df.to_excel(writer, index=False, sheet_name='Sheet1', startrow=2)
        # 获取工作簿和工作表
        workbook = writer.book
        sheet = workbook['Sheet1']
        # 合并第一行的所有列，添加“报销单”并居中
        max_column = sheet.max_column  # 获取列数
        sheet.merge_cells(start_row=1, start_column=1, end_row=1, end_column=max_column)  # 合并第一行的所有列
        cell = sheet.cell(row=1, column=1)
        cell.value = name  # 设置单元格内容
        cell.alignment = Alignment(horizontal='center', vertical='center')  # 设置居中对齐

        # # 将列名放到第二行
        # for col_num, title in enumerate(df.columns, 1):  # 将 DataFrame 的列名放到第二行
        #     sheet.cell(row=2, column=col_num, value=title)

        sheet.column_dimensions['A'].width = 15
        for row in sheet['A'][0:]:  # 从第二行开始遍历 A 列
            row.alignment = Alignment(horizontal='left')  # 设置 A 列内容居左对齐
        sheet.column_dimensions['C'].width = 40

        amount_column = 5
        for row in sheet.iter_rows(min_row=3, min_col=amount_column, max_col=amount_column):
            for cell in row:
                cell.number_format = '#,##0.00'  # 设置数字格式


        # 获取最后一行的行号
        last_row = sheet.max_row

        # 将合计金额写入最后一行的 E 列
        sheet.cell(row=last_row + 1, column=1, value="合计金额")  # 在 E 列写入 "合计金额"
        sheet.cell(row=last_row + 1, column=5, value=total_amount)  # 在 F 列写入合计金额的数值

        # 将指针移动到开头
        output.seek(0)
    #生成文件
    path = random_filename()+".xlsx"
    url = os.path.join(settings.EXCEL_FOLDER + "/" + path)
    with open(url, 'wb') as f:
        f.write(output.getvalue())
    #写入数据库
    user_id = CTX_USER_ID.get()
    await Downloads.create(name=name,state=1,type=2,user_id=user_id,url=url,fapiao_count =1)
    return True


def date_format(date):
    return date.strftime(settings.DATE_FORMAT)


async def process_url(url: str):
    try:
        # 解析 URL
        parsed_url = urlparse(url)
        # 获取路径部分
        clean_url = parsed_url.path
        # 如果需要去掉路径中的其他不需要的部分，可以进一步处理
        return clean_url.lstrip('/')
    except Exception as e:
        logger.info(f"process_url出错 {e}")




async def image_to_pdf(image_path, pdf_path):
    try:
        # 打开图片文件
        img = fitz.open(image_path)  # 这里的 `fitz.open` 支持图片格式
        logger.info(f"开始转换图片: {image_path}")
        # 创建一个新的 PDF 文档
        pdf_document = fitz.open()
        # 获取图片的矩形框，定义页面大小
        rect = img[0].rect
        pdf_document.new_page(width=rect.width, height=rect.height)
        # 将图片插入到新页面中
        pdf_document[-1].insert_image(rect, filename=image_path)
        # 保存 PDF 文件
        pdf_document.save(pdf_path)
    except Exception as e:
        logger.info(f"转换图片 {image_path} 到 PDF 时出错: {e}")